﻿using System.Collections; 
using UnityEngine;
using System.Collections.Generic;
using Mono.Data.Sqlite;
 
public class SqlManager : MonoBehaviour {
 
    private const string dbName = "EditQuit";
 
    //链接数据库
    private SqliteConnection connection;
    //数据库命令
    private SqliteCommand command;
    //数据库阅读器
    private SqliteDataReader reader;
 
    private static SqlManager _instance = null;

    public static SqlManager Instance
    {
        get
        {
            if (_instance == null)
            {
                GameObject obj = new GameObject();
                _instance = obj.AddComponent<SqlManager>();
            }
            return _instance;
        }
    }
 
    void Awake()
    {
           Debug.Log("DataBase  Awake");
        if(null == _instance){
            _instance = this;
        }else{
            GameObject.Destroy(this);
            return;
        }
 
        //初始化，打开数据库
        OpenDB(dbName);
    }
 
    void OnDestroy() {
         CloseDB();
    }
   // private SqlManager() { }
   
   // public SqlManager(string dbName)
    // {
     //   OpenDB(dbName);
    // } 
 
    public void OpenDB(string dbName)
    {
        try
        {   //先关闭
            if(connection!=null) 
                CloseDB(); 
            
            string dbPath = Application.streamingAssetsPath + "/KFConfig/" + dbName + ".cc";
            //固定sqlite格式data source
            connection = new SqliteConnection(@"Data Source = " + dbPath);
            connection.Open();
 
            Debug.Log("DataBase Connect");
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.ToString());
        }
    }
    public void CloseDB()
    {
        /*
         * IDispose接口可以通过Using(){}关键字实现使用后立刻销毁
         * Close （）方法回滚任何挂起的事务。然后，它将连接释放到连接池，或者在连接池被禁用的情况下关闭连接，
         * 应用程序可以多次调用 Close。不会生成任何异常。
         * Dispose（）方法实际是和close（）做的同一件事，唯一的区别是Dispose方法清空了connectionString，即设置为了null.
         */
        if (reader != null)
            reader.Close();
 
        if (command != null)
            command.Dispose();
 
        if (connection != null)
            connection.Close();
 
        Debug.Log("DataBase Close");
    }
 
    //创建数据库表
    public void CreateTabel(string _tableName,string[] col,string[] colType)
    {
        //string createSql = "CREATE TABLE player(ID int,name text,damage int)";
        if (col.Length != colType.Length)
        {
            Debug.LogError("Colum's Length != ColumType's Length");
            return;
        }
 
        string sql = "CREATE TABLE "+_tableName+"(";
        for(int i=0;i<col.Length;i++)
        {
            sql += col[i] + " " + colType[i] + ",";
        }
        sql = sql.TrimEnd(',');
        sql += ")";
 
        ExcuteSql(sql);
    }
 
    //插入数据
    public void Insert(string _tableName,object[] values)
    {
        //string createSql = "INSERT INTO player(ID,name,damage) VALUES (102,'ak47',120)";
        if (values.Length == 0)
            Debug.LogError("Values's length == 0");
 
        string sql = "INSERT INTO "+_tableName + " VALUES(";
 
        foreach(object value in values)
        {
            sql += "'" + value.ToString() + "'" +",";
        }
        sql = sql.TrimEnd(',');
        sql += ")";
 
        ExcuteSql(sql);
    }
    //删除数据 OR
    public void DeleteOR(string _tabelName, params object[] colums)
    {
        //string createSql = "delete from player where rowid=1";
        if (colums == null || colums.Length == 0)
            Debug.LogError("colums == null || colums.Length == 0");
 
        string sql = "DELETE FROM " + _tabelName + " WHERE ";
 
        for (int i = 0; i < colums.Length; i += 2)
        {
            sql += colums[i] + " = '" + colums[i + 1].ToString() + "' OR ";
        }
        sql = sql.Remove(sql.Length - 3);
        ExcuteSql(sql);
    }
 
    //删除数据 AND
    public void DeleteAND(string _tabelName, params object[] colums)
    {
        if (colums == null || colums.Length == 0)
            Debug.LogError("colums == null || colums.Length == 0");
 
        string sql = "DELETE FROM " + _tabelName + " WHERE ";
 
        for (int i=0;i<colums.Length;i+=2)
        {
            sql += colums[i] + " = '" + colums[i+1].ToString() + "' AND ";
        }
        sql = sql.Remove(sql.Length - 4);
        ExcuteSql(sql);
    }
 
    //更新数据 OR
    public void UpdateOR(string _tableNmae,object[] colums, params object[] options)
    {
        //string createSql = "Update player set name='M16' where id=102";
        if (colums == null || colums.Length == 0 || colums.Length % 2 == 1)
            Debug.LogError("colums Length has error!!!");
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");
 
        string sql = "UPDATE " + _tableNmae + " SET ";
        for (int i = 0; i < colums.Length; i += 2)
        {
            sql += colums[i] + " =  '" + colums[i + 1].ToString() + "' ";
        }
        sql += " WHERE ";
 
        for (int i = 0; i < options.Length; i+=2)
        {
            sql += options[i] + "=" + options[i+1].ToString() + " OR ";
        }
        sql = sql.Remove(sql.Length - 3);
 
        ExcuteSql(sql);
    }
 
    //更新数据 AND
    public void UpdateAND(string _tableNmae, object[] colums, params object[] options)
    {
        //string createSql = "Update player set name='M16' where id=102";
        if (colums == null || colums.Length == 0 || colums.Length % 2 == 1)
            Debug.LogError("colums Length has error!!!");
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");
 
        string sql = "UPDATE " + _tableNmae + " SET ";
        for (int i = 0; i < colums.Length; i += 2)
        {
            sql += colums[i] + " =  '" + colums[i + 1].ToString() + "' ";
        }
        sql += " WHERE ";
 
        for (int i = 0; i < options.Length; i += 2)
        {
            sql += options[i] + "=" + options[i + 1].ToString() + " AND ";
        }
        sql = sql.Remove(sql.Length - 4);
 
        ExcuteSql(sql);
    }
  
 
    //查找所有
    public List<Dictionary<string, object>> SelectAll(string _tableName)
    {
        string sql = "SELECT * FROM " + _tableName;
        List<Dictionary<string, object>> dataArr = new List<Dictionary<string, object>>();
        reader = ExcuteSql(sql);
        while (reader.Read())
        {
            Dictionary<string, object> data = new Dictionary<string, object>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string key = reader.GetName(i);
                object value = reader.GetValue(i);
                data.Add(key, value);
            }
            dataArr.Add(data);
        }
        return dataArr;
    }
 
    //按照自定义条件查找单条数据
    public Dictionary<string,object> SelectWithCondition(string _tableName,params object[] options)
    {
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");
 
        string sql = "SELECT * FROM " + _tableName + " WHERE ";
        Dictionary<string, object> dic = new Dictionary<string, object>();
 
        for(int i=0;i< options.Length;i+=2)
        {
            sql += options[i] + "= '" + options[i + 1]+"' AND ";
        }
        sql = sql.Remove(sql.Length-4);
 
        reader = ExcuteSql(sql);
        reader.Read();
        for(int i=0;i<reader.FieldCount;i++)
        {
            string key = reader.GetName(i);
            object value = reader.GetValue(i);
            dic.Add(key, value);
        }
        return dic;
    }
 
    //按照自定义条件查找整张表数据
    public List<Dictionary<string, object>> SelectAllWithCondition(string _tableName, params object[] options)
    {
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");
 
        string sql = "SELECT * FROM " + _tableName + " WHERE ";
        List<Dictionary<string, object>> dataArr = new List<Dictionary<string, object>>();
 
        for (int i = 0; i < options.Length; i += 2)
        {
            sql += options[i] + "= '" + options[i + 1] + "' AND ";
        }
        sql = sql.Remove(sql.Length - 4);
 
        reader = ExcuteSql(sql);
        while (reader.Read())
        {
            Dictionary<string, object> data = new Dictionary<string, object>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string key = reader.GetName(i);
                object value = reader.GetValue(i);
                data.Add(key, value);
            }
            dataArr.Add(data);
        }
        return dataArr;
    }
 
    //执行数据库语句
    SqliteDataReader ExcuteSql(string _sql)
    {
        Debug.Log("Excuted Sql :" + _sql);
        //创建数据库连接命令（事务管理、命令管理：向数据库发送指令）
        command = connection.CreateCommand();
        //设置命令语句
        command.CommandText = _sql;
        //执行命令后 返回 阅读器信息
        using (reader = command.ExecuteReader())
        {
            return reader;
        }
    }
}
